﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Transcend.DP3.Db
{

	public class OraDMLExporter
	{

		public Database Db { get; set; }

		public OraDMLExporter(Database db)
		{
			this.Db = db;
		}

		/// <summary>
		/// 生成insert语句
		/// </summary>
		/// <param name="st"></param>
		/// <param name="tableName">查询语句</param>
		/// <param name="maxRow">最大条数</param>
		/// <param name="excFields">排除字段</param>
		public void ExpInsertDML(System.IO.TextWriter st, string tableName, string where, int maxRow, params string[] excFields)
		{
			var rowBuf = new StringBuilder();
			var sql = string.Format("select * from {0}", tableName);
			if (!string.IsNullOrEmpty(where))
			{
				sql += " where " + where;
			}

			using (var cmd = Db.mkCommand(sql))
			{
				using (var dr = cmd.ExecuteReader())
				{
					var fields = Database.GetFields(dr, excFields);
					while (dr.Read())
					{
						rowBuf.Append("insert into ");
						rowBuf.Append(tableName);
						rowBuf.Append(" (");
						rowBuf.Append(string.Join(",", fields));
						rowBuf.Append(")values(");
						for (var i = 0; i < fields.Length; i++)
						{
							if (i > 0) rowBuf.Append(',');
							rowBuf.Append(SqlValue(dr, fields[i]));
						}
						rowBuf.Append(");");
						st.WriteLine(rowBuf);
						rowBuf.Clear();
						if (--maxRow == 0)
						{
							break;
						}
					}
				}
			}
		}

		public void ExpUpdateDML(System.IO.TextWriter st, string tableName, string where, int maxRow, string updateBy, params string[] excFields)
		{

			var rowBuf = new StringBuilder();
			var sql = string.Format("select * from {0}", tableName);
			if (!string.IsNullOrEmpty(where))
			{
				sql += " where " + where;
			}

			using (var cmd = Db.mkCommand(sql))
			{
				using (var dr = cmd.ExecuteReader())
				{
					var fields = Database.GetFields(dr, excFields);
					while (dr.Read())
					{
						rowBuf.Append("update ");
						rowBuf.Append(tableName);
						rowBuf.Append(" set (");
						for (var i = 0; i < fields.Length; i++)
						{
							if (i > 0) rowBuf.Append(',');
							rowBuf.Append(string.Format("{0}]{1}", fields[i], SqlValue(dr, fields[i])));
						}
						rowBuf.Append(") where ");
						st.WriteLine(rowBuf);
						if (--maxRow == 0)
						{
							break;
						}
					}
				}
			}
		}

		public string[] GetSchemas()
		{
			var users = new System.Collections.Specialized.StringCollection();
			var sql = "select username from all_users order by created desc";
			using (var cmd = Db.mkCommand(sql))
			{
				using (var dr = cmd.ExecuteReader())
				{
					while (dr.Read())
					{
						users.Add(Transcend.DP3.Db.Database.GetValue<string>(dr, 0));
					}
				}
			}

			var schemas = new string[users.Count];
			users.CopyTo(schemas, 0);
			return schemas;
		}

		public string[] GetTables(params string [] schemas)
		{
			var tableCol = new System.Collections.Specialized.StringCollection();
			var sql = "select owner, table_name from all_tables";
			if (schemas != null)
			{
				sql += " where owner in (";
				for (var i = 0; i < schemas.Length; i++)
				{
					if (i > 0) sql += ",";
					sql += string.Format(":p{0}", i);
				}
				sql += ")";
			}
			sql += " order by owner";
			using (var cmd = Db.mkCommand(sql, schemas))
			{
				using (var dr = cmd.ExecuteReader())
				{
					while (dr.Read())
					{
						tableCol.Add(Transcend.DP3.Db.Database.GetValue<string>(dr, 0));
						tableCol.Add(Transcend.DP3.Db.Database.GetValue<string>(dr, 1));
					}
				}
			}

			var tables = new string[tableCol.Count];
			tableCol.CopyTo(tables, 0);
			return tables;
		}

		protected static string SqlValue(System.Data.Common.DbDataReader dr, string fieldName)
		{
			var oVal = dr[fieldName];
			var sVal = string.Empty;
			switch (Type.GetTypeCode(oVal.GetType()))
			{
				case TypeCode.Byte:
				case TypeCode.Char:
				case TypeCode.Decimal:
				case TypeCode.Double:
				case TypeCode.Int16:
				case TypeCode.Int32:
				case TypeCode.Int64:
				case TypeCode.Object:
				case TypeCode.SByte:
				case TypeCode.Single:
				case TypeCode.UInt16:
				case TypeCode.UInt32:
				case TypeCode.UInt64:
					sVal = Convert.ToString(oVal);
					break;
				case TypeCode.Boolean:
					sVal = Convert.ToString(oVal);
					break;
				case TypeCode.String:
					sVal = string.Format("'{0}'", Transcend.DP3.Helper.EscapeHelper.EscapeSqlKeywords(oVal as string));
					break;
				case TypeCode.DateTime:
					sVal = string.Format("to_date('{0}','yyyy-mm-dd hh24:mi:ss')", oVal);
					break;
				case TypeCode.Empty:
				case TypeCode.DBNull:
					sVal = "null";
					break;
				default:
					sVal = string.Format("'{0}'", oVal);
					break;
			}

			return sVal;
		}

	}


}
